package com.shb.jutils.commons;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class JdbcUtil {
//	public static String url = "jdbc:oracle:thin:@172.23.8.88:1521:orcl";
//	public static String user = "yxgl";
//	public static String pass = "yxgl";
	
	public static String url = "jdbc:mysql://127.0.0.1:3306/itcms";
	public static String user = "root";
	public static String pass = "123456";
	public static String driverName = "com.mysql.jdbc.Driver";
	
	private static Connection conn = null;
	private static PreparedStatement pstmt = null;
	private static ResultSet rs = null;
	
	public static void init(String url, String user, String pass, String driverName){
		JdbcUtil.url = url;
		JdbcUtil.user = user;
		JdbcUtil.pass = pass;
		JdbcUtil.driverName = driverName;
	}
	
	/**
	 * 获取jdbc连接
	 * @return
	 */
	public static Connection getConnection (){
		try{
			//Class.forName("oracle.jdbc.driver.OracleDriver");
			Class.forName(driverName);
			conn = DriverManager.getConnection(url, user, pass);
		}catch(ClassNotFoundException e){
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}
	
	/**
	 * 执行查询
	 * @param sql
	 * @return
	 */
	public static ResultSet find(String sql){
		try {
			conn = getConnection();
			pstmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
			rs = pstmt.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;
	}
	
	/**
	 * 带参数的查询
	 * @param sql
	 * @param params
	 * @return
	 */
	public static ResultSet find(String sql,String[] params){
		try {
			conn = getConnection();
			pstmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
			for(int i = 0; i< params.length; i++){
				pstmt.setObject(i+1, params[i]);
			}
			rs = pstmt.executeQuery();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		return rs;
	}
	
	/**
	 * 执行增删改语句
	 * @param sql
	 */
	public static int excute(String sql){
		int rows;
		try {
			conn = getConnection();
			pstmt = conn.prepareStatement(sql);
			rows = pstmt.executeUpdate();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}finally{
			close();
		}
		return rows;
	}
	
	/**
	 * 执行带参数的增删改语句
	 * @param sql
	 * @param params
	 */
	public static int excute(String sql, String[] params){
		int rows;
		try {
			conn = getConnection();
			pstmt = conn.prepareStatement(sql);
			for(int i = 0; i< params.length; i++){
				pstmt.setObject(i+1, params[i]);
			}
			rows = pstmt.executeUpdate();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}finally{
			close();
		}
		return rows;
	}
	
	/**
	 * 释放连接
	 */
	public static void close(){
		if(rs != null){
			try {
				rs.close();
			} catch (SQLException e) {
				rs = null;
			}
		}
		
		if(pstmt != null){
			try {
				pstmt.close();
			} catch (SQLException e) {
				pstmt = null;
			}
		}
		
		if(conn != null){
			try {
				conn.close();
			} catch (SQLException e) {
				conn = null;
			}
		}
	}
}
